package jdbc;

import utils.JdbcUtils;

import java.sql.*;

public class JdbcDemo {
    public static void main(String[] args) {
//        showDatas();
//        insertData();
//        insertPrepareData();
//        batchInsertData();
    }

    private static void batchInsertData() throws Exception {
        /*
        * 批量处理
        * */
        Connection conn = null;
        Statement stmt = null;

        try {
            conn = JdbcUtils.getConnection();
            conn.setAutoCommit(false); // 不自动提交

            stmt = conn.createStatement(); // 返回一个用于执行静态SQL语句的对象
            stmt.addBatch("insert into rules values(49, 'test', 'http://www.baidu.com', 'null', '//table', 1)");
            stmt.addBatch("insert into rules values(55, 'test', 'http://www.baidu.com', 'null', '//table', 1)");
            stmt.executeBatch();
            conn.commit();
            conn.setAutoCommit(true);

        }catch(ExceptionInInitializerError e){
            e.printStackTrace();
        }catch(SQLException e){
            try {
                if(conn != null) {
                    conn.rollback();
                    conn.setAutoCommit(true);
                }
            } catch (SQLException e1) {
                e1.printStackTrace();
            }

            e.printStackTrace();
        }finally {
            JdbcUtils.free(stmt, conn, null);
        }
    }

    private static void insertPrepareData() {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/zzh", "root", "123456");
            pstmt = conn.prepareStatement("insert into rules values(?,?,?,?,?,?)"); // 返回预编译的SQL语句对象，使用此对象多次高效地执行SQL语句
            pstmt.setInt(1, 50);
            pstmt.setString(2, "test");
            pstmt.setString(3, "http://www.baidu.com");
            pstmt.setString(4, "sss");
            pstmt.setString(5, "//div");
            pstmt.setInt(6, 1);
            pstmt.executeUpdate();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e){
            e.printStackTrace();
        }finally {
            try {
                if (pstmt != null)
                    pstmt.close();
                if (conn != null)
                    conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    private static void insertData() {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver"); // 加载JDBC驱动程序
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/zzh", "root", "123456"); // 创建数据库连接
            stmt = conn.createStatement(); // 返回一个用于执行静态SQL语句的对象
            String sql = "insert into rules values(46, 'test', 'http://www.baidu.com', 'null', '//table', 1)";
            int bool = stmt.executeUpdate(sql);
            System.out.println(bool);
        }catch(ClassNotFoundException e){
            e.printStackTrace();
        }catch(SQLException e){
            e.printStackTrace();
        }finally {
            try {

                if (stmt != null)
                    stmt.close();
                if (conn != null)
                    conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    private static void showData() {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver"); // 加载JDBC驱动程序
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/zzh", "root", "123456"); // 创建数据库连接
            stmt = conn.createStatement(); // 返回一个用于执行静态SQL语句的对象
            rs = stmt.executeQuery("select * from rules");
            while (rs.next()) {
                System.out.println(rs.getString("name"));
            }
        }catch(ClassNotFoundException e){
            e.printStackTrace();
        }catch(SQLException e){
            e.printStackTrace();
        }finally {
            try {
                if (rs != null)
                    rs.close();
                if (stmt != null)
                    stmt.close();
                if (conn != null)
                    conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
